import psycopg2
import psycopg2.extras
import pymysql
import pymysql.cursors
from config import AUTHORIZATION_DB_CONFIG, AUTHORIZATION_DB_MYSQL_CONFIG, AUTHORIZATION_DB_TYPE
from typing import List, Dict, Any, Optional, Tuple
import json

class AuthorizationService:
    def __init__(self, db_config=None):
        """
        初始化权限服务
        
        Args:
            db_config: 数据库连接配置
        """
        self.db_type = AUTHORIZATION_DB_TYPE
        if db_config:
            self.db_config = db_config
        else:
            if self.db_type == 'openGauss':
                self.db_config = AUTHORIZATION_DB_CONFIG
            else:
                # 如果是MySQL，确保使用db而不是dbname
                mysql_config = AUTHORIZATION_DB_MYSQL_CONFIG.copy()
                if 'dbname' in mysql_config:
                    mysql_config['db'] = mysql_config.pop('dbname')
                self.db_config = mysql_config
        
    def _get_connection(self):
        """获取数据库连接"""
        if self.db_type == 'openGauss':
            return psycopg2.connect(**self.db_config, cursor_factory=psycopg2.extras.RealDictCursor)
        else:
            return pymysql.connect(**self.db_config, cursorclass=pymysql.cursors.DictCursor)
    
    def _get_placeholder(self):
        """根据数据库类型返回不同的SQL占位符"""
        return '%s' if self.db_type == 'openGauss' else '%s'
    
    def _get_schema_prefix(self):
        """根据数据库类型返回schema前缀"""
        if self.db_type == 'openGauss':
            # return 'hos_bi.'
            return 'sm_dmp.'
        else:
            return 'dmp_bi2.'
        
    def check_indicator_permissions(self, user_id: str, indicators: List[Dict]) -> Tuple[List[Dict], List[Dict], str]:
        """
        检查用户对指标的权限
        
        Args:
            user_id: 用户ID
            indicators: 指标列表
            
        Returns:
            Tuple[List[Dict], List[Dict], str]: 
                - 有权限的指标列表（包含权限ID）
                - 无权限的指标列表
                - 错误信息(如果有)
        """
        if not indicators:
            return [], [], "没有指标需要检查权限"
            
        authorized_indicators = []
        unauthorized_indicators = []
        schema_prefix = self._get_schema_prefix()
        
        try:
            conn = self._get_connection()
            cursor = conn.cursor()
            
            for indicator in indicators:
                indicator_code = indicator.get('code')
                if not indicator_code:
                    unauthorized_indicators.append(indicator)
                    continue
                    
                # 查询用户对该指标的权限
                query = f"""
                SELECT id FROM {schema_prefix}data_authorization 
                WHERE type = 'user' 
                AND allocation_object_id = {self._get_placeholder()}
                AND authorized_object_type = 'index'
                AND authorized_object_code = {self._get_placeholder()}
                """
                cursor.execute(query, (user_id, indicator_code))
                result = cursor.fetchone()
                print(f"检查权限: user_id={user_id}, indicator_code={indicator_code}, 查询结果={result}")
                if result:
                    # 将权限ID添加到指标信息中
                    authorized_indicator = indicator.copy()
                    authorized_indicator['auth_id'] = result['id']
                    authorized_indicators.append(authorized_indicator)
                else:
                    unauthorized_indicators.append(indicator)
            
            cursor.close()
            conn.close()
            
            return authorized_indicators, unauthorized_indicators, ""
            
        except Exception as e:
            error_msg = f"检查指标权限时发生错误: {str(e)}"
            print(error_msg)
            return [], indicators, error_msg

    def get_authorized_dimensions_and_filters(self, auth_ids: List[int]) -> Tuple[List[Dict], List[Dict], str]:
        """
        根据auth_ids获取有权限的维度列表和过滤条件列表
        
        Args:
            auth_ids (List[int]): 主表data_authorization的id列表
            
        Returns:
            Tuple[List[Dict], List[Dict], str]:
                - 有权限的维度列表，每个维度包含 'code' 和 'table'
                - 过滤条件列表，每个过滤条件包含 'code', 'table', 'operator', 'value'
                - 错误信息(如果有)
        """
        if not auth_ids:
            return [], [], "没有权限ID"
            
        try:
            conn = self._get_connection()
            cursor = conn.cursor()
            schema_prefix = self._get_schema_prefix()
            
            placeholders = ','.join([self._get_placeholder()] * len(auth_ids))
            
            # MySQL和OpenGauss的JSON处理函数不同
            json_build_func = 'JSON_OBJECT' if self.db_type == 'mysql' else 'json_build_object'
            array_agg_func = 'GROUP_CONCAT' if self.db_type == 'mysql' else 'array_agg'
            
            query = f"""
            WITH user_auth AS (
                SELECT 
                    da.id,
                    da.authorized_object_code as auth_obj,
                    dai.item_code as code,
                    dai.item_names as name,
                    dai.filter_operator,
                    dai.filter_value,
                    dai.dict_id as related_code_table
                FROM {schema_prefix}data_authorization da
                INNER JOIN {schema_prefix}data_authorization_item dai 
                ON da.id = dai.data_authorization_id
                WHERE dai.data_authorization_id IN ({placeholders})
            )
            SELECT 
                auth_obj,
                code,
                name,
                filter_operator,
                filter_value,
                related_code_table
            FROM user_auth
            """
            
            cursor.execute(query, tuple(auth_ids))
            rows = cursor.fetchall()
            print(f"auth_ids 输入为: {auth_ids}")
            print(f"最终 SQL 为:\n{query}")
            print(f"检查权限: auth_ids={auth_ids}, 查询结果数量={len(rows)}")
            dimensions = []
            filters = []
            seen_dims = set()
            seen_filters = set()
            
            for row in rows:
                dim_key = row['code']
                if dim_key not in seen_dims:
                    dimensions.append({
                        'dimension_code': row['code'],
                        'dimension_description': row['name'],
                        'related_code_table': row['related_code_table'],
                        'auth_obj': row['auth_obj']
                    })
                    seen_dims.add(dim_key)
                    
                if row.get('filter_operator') and row.get('filter_value'):
                    filter_key = (row['code'], row['filter_operator'], row['filter_value'])
                    if filter_key not in seen_filters:
                        filters.append({
                            'field': row['code'],
                            'operator': row['filter_operator'],
                            'value': row['filter_value']
                        })
                        seen_filters.add(filter_key)
            
            cursor.close()
            conn.close()
            
            return dimensions, filters, ""
            
        except Exception as e:
            error_msg = f"获取维度和过滤条件出错: {str(e)}"
            print(error_msg)
            return [], [], error_msg

    def parse_values(self, value_str):
        """
        将逗号分隔的字符串转换为集合，去除多余空格。
        适用于 in、not in 等集合类条件。
        """
        return {v.strip() for v in value_str.split(",") if v.strip()}

    def parse_range(self, value_str):
        """
        将表示范围的字符串（格式：'lower,upper'）解析为 (lower, upper) 元组。
        若解析失败，则返回 None。
        """
        try:
            parts = [float(x.strip()) for x in value_str.split(",") if x.strip()]
            if len(parts) != 2:
                return None
            return parts[0], parts[1]
        except Exception:
            return None

    def operator_type(self, op):
        """
        根据操作符判断条件类型：数值型或集合型。
        """
        if op in ["=", ">", ">=", "<", "<=", "between"]:
            return "numeric"
        elif op in ["in", "not in", "like"]:
            return "set"
        else:
            return "unknown"

    def numeric_interval(self, cond):
        """
        将数值类型条件转换为区间形式，返回 (lower, upper, lower_inclusive, upper_inclusive)。
        例如：
        "=": [v, v]
        ">": (v, +∞)
        ">=": [v, +∞)
        "<": (-∞, v)
        "<=": (-∞, v]
        "between": [lower, upper]
        若转换失败，返回 None。
        """
        op = cond["operator"]
        try:
            if op == "=":
                v = float(cond["value"])
                return (v, v, True, True)
            elif op == ">":
                v = float(cond["value"])
                return (v, float("inf"), False, True)
            elif op == ">=":
                v = float(cond["value"])
                return (v, float("inf"), True, True)
            elif op == "<":
                v = float(cond["value"])
                return (float("-inf"), v, True, False)
            elif op == "<=":
                v = float(cond["value"])
                return (float("-inf"), v, True, True)
            elif op == "between":
                rng = self.parse_range(cond["value"])
                if rng is None:
                    return None
                lower, upper = rng
                return (lower, upper, True, True)
        except Exception:
            return None

    def intersect_intervals(self, interval1, interval2):
        """
        计算两个数值区间的交集，区间格式为 (lower, upper, lower_inclusive, upper_inclusive)。
        若交集为空，返回 None。
        """
        lower1, upper1, li1, ui1 = interval1
        lower2, upper2, li2, ui2 = interval2

        new_lower = max(lower1, lower2)
        # 判断下界的包含性：若两个下界相等，则只有当两边都包含时，新下界才包含
        if lower1 == lower2:
            new_lower_inclusive = li1 and li2
        elif new_lower == lower1:
            new_lower_inclusive = li1
        else:
            new_lower_inclusive = li2

        new_upper = min(upper1, upper2)
        if upper1 == upper2:
            new_upper_inclusive = ui1 and ui2
        elif new_upper == upper1:
            new_upper_inclusive = ui1
        else:
            new_upper_inclusive = ui2

        # 判断区间是否有效
        if new_lower < new_upper:
            return (new_lower, new_upper, new_lower_inclusive, new_upper_inclusive)
        elif new_lower == new_upper and new_lower_inclusive and new_upper_inclusive:
            return (new_lower, new_upper, True, True)
        else:
            return None

    def interval_to_condition(self, field, interval):
        """
        将区间转换回条件表示：
        - 若下界和上界相等，则使用 "="；
        - 否则统一使用 "between" 表示区间。
        """
        lower, upper, li, ui = interval
        if lower == upper and li and ui:
            return {"field": field, "operator": "=", "value": str(lower)}
        else:
            return {"field": field, "operator": "between", "value": f"{lower},{upper}"}

    def combine_set_conditions(self, field, cond1, cond2):
        """
        合并集合类型条件（操作符 in、not in、like、或 "=" 被视为单值集合）。
        处理思路：
        1. 若双方都是 in 或 "="（视 "=" 为单元素 in），取集合交集；
        2. 若一边为 not in，另一边为 in 或 "="，则允许集合为 (in集合 - not in集合)；
        3. 若双方均为 not in，则返回 not in 限制的并集；
        4. like 的处理较简单：如果 one 是 "=" 与 one是 "like"，则检查 "=" 的值是否符合 like 模式（这里简单处理：如果 like 模式以 "%" 结尾，则要求值以指定前缀开始）。
        若无法合并，则返回 None。
        """
        op1 = cond1["operator"]
        op2 = cond2["operator"]
        val1 = cond1["value"].strip()
        val2 = cond2["value"].strip()

        def to_set(op, val):
            if op == "=":
                return {val}
            elif op in ["in", "not in"]:
                return self.parse_values(val)
            elif op == "like":
                # 处理不同的like模式
                if val.startswith("%") and val.endswith("%"):
                    # %value% 模式：包含匹配
                    return {"contains": val[1:-1]}
                elif val.endswith("%"):
                    # value% 模式：前缀匹配
                    return {"startswith": val[:-1]}
                elif val.startswith("%"):
                    # %value 模式：后缀匹配
                    return {"endswith": val[1:]}
                return {val}  # 精确匹配
            return {val}
        
        # 处理 like 和 in 的交集
        if op1 == "like" and op2 == "in":
            like_set = to_set(op1, val1)
            allowed_values = self.parse_values(val2)
            
            # 根据不同的like模式进行匹配
            if isinstance(like_set, dict):
                if "contains" in like_set:
                    matching_values = {v for v in allowed_values if like_set["contains"] in v}
                elif "startswith" in like_set:
                    matching_values = {v for v in allowed_values if v.startswith(like_set["startswith"])}
                elif "endswith" in like_set:
                    matching_values = {v for v in allowed_values if v.endswith(like_set["endswith"])}
                else:
                    matching_values = like_set & allowed_values
            else:
                matching_values = like_set & allowed_values
                
            if matching_values:
                return {"field": field, "operator": "in", "value": ",".join(matching_values)}
            return None
        elif op2 == "like" and op1 == "in":
            return self.combine_set_conditions(field, cond2, cond1)  # 交换参数重用逻辑

        # 同为 in 或 "=" 或 not in 类型
        if op1 in ["in", "=","not in"] and op2 in ["in", "=","not in"]:
            set1 = to_set(op1, val1)
            set2 = to_set(op2, val2)
            # 如果双方都是允许类型（in 或 =）
            if op1 in ["in", "="] and op2 in ["in", "="]:
                common = set1.intersection(set2)
                if common:
                    if op1 == "=" and op2 == "=":
                        return {"field": field, "operator": "=", "value": list(common)[0]}
                    else:
                        return {"field": field, "operator": "in", "value": ",".join(common)}
                else:
                    return None
            # 一边是 not in，一边是 in 或 "="
            elif op1 == "not in" and op2 in ["in", "="]:
                allowed = set2.difference(set1)
                if allowed:
                    if op2 == "=" and len(allowed) == 1:
                        return {"field": field, "operator": "=", "value": list(allowed)[0]}
                    else:
                        return {"field": field, "operator": "in", "value": ",".join(allowed)}
                else:
                    return None
            elif op2 == "not in" and op1 in ["in", "="]:
                allowed = set1.difference(set2)
                if allowed:
                    if op1 == "=" and len(allowed) == 1:
                        return {"field": field, "operator": "=", "value": list(allowed)[0]}
                    else:
                        return {"field": field, "operator": "in", "value": ",".join(allowed)}
                else:
                    return None
            # 双方均为 not in：合并排除值（并集）
            elif op1 == "not in" and op2 == "not in":
                union_set = set1.union(set2)
                return {"field": field, "operator": "not in", "value": ",".join(union_set)}
            else:
                return None
        # 包含 like 的情况处理：目前只处理 "=" 与 "like" 的组合
        elif (op1 == "like" and op2 == "=") or (op1 == "=" and op2 == "like"):
            if op1 == "=":
                value = val1
                pattern = val2
            else:
                value = val2
                pattern = val1
            # 简单处理：如果 like 模式以 "%" 结尾，则检查前缀匹配
            if pattern.endswith("%"):
                prefix = pattern[:-1]
                if value.startswith(prefix):
                    return {"field": field, "operator": "=", "value": value}
                else:
                    return None
            else:
                if value == pattern:
                    return {"field": field, "operator": "=", "value": value}
                else:
                    return None
        # 若双方均为 like，则只有完全一致才认为匹配
        elif op1 == "like" and op2 == "like":
            if val1 == val2:
                return {"field": field, "operator": "like", "value": val1}
            else:
                return None
        else:
            return None

    def combine_conditions(self, field, cond1, cond2):
        """
        合并同一字段下来自 service_filters 与 client_filters 的两个条件，
        尝试取两者的交集，返回一个更严格的条件表示。
        处理分为两大类：
        1. 数值类型条件：转换为区间后计算交集；
        2. 集合类型条件：转换为集合后计算交集。
        如果条件类型不同，尝试处理特殊情况（例如 "=" 与 "in"）。
        若无法合并，则返回 None。
        """
        type1 = self.operator_type(cond1["operator"])
        type2 = self.operator_type(cond2["operator"])
        if type1 == "numeric" and type2 == "numeric":
            interval1 = self.numeric_interval(cond1)
            interval2 = self.numeric_interval(cond2)
            if interval1 is None or interval2 is None:
                return None
            inter = self.intersect_intervals(interval1, interval2)
            if inter is None:
                return None
            return self.interval_to_condition(field, inter)
        elif type1 == "set" and type2 == "set":
            return self.combine_set_conditions(field, cond1, cond2)
        else:
            # 如果类型不同，尝试处理 "=" 与 "in" 的特殊情况
            if cond1["operator"] == "=" and type2 == "set":
                value = cond1["value"].strip()
                allowed = self.parse_values(cond2["value"])
                if value in allowed:
                    return {"field": field, "operator": "=", "value": value}
                else:
                    return None
            elif cond2["operator"] == "=" and type1 == "set":
                value = cond2["value"].strip()
                allowed = self.parse_values(cond1["value"])
                if value in allowed:
                    return {"field": field, "operator": "=", "value": value}
                else:
                    return None
            else:
                return None

    def compute_filter_intersection(self, service_filters, client_filters) -> Tuple[List[Dict], str]:
        """
        计算最终过滤条件，处理逻辑如下：
        1. 对于 service_filters 和 client_filters 中，字段只存在于一方的条件，直接保留；
        2. 对于双方都有的同一字段，尝试合并（取交集）：
            - 若合并成功，则保留合并后的条件；
            - 若合并失败（返回 None），则说明该字段条件无法同时满足，返回"没有权限访问"。
        3. 如果最终条件列表为空，则返回"没有权限访问"。
        """
        try:
            final_filters = []
            # 建立以字段为 key 的字典（假设每个列表中每个字段只出现一次；若有多次可先合并）
            service_dict = {cond["field"]: cond for cond in service_filters}
            client_dict = {cond["field"]: cond for cond in client_filters}

            # 获取所有涉及的字段
            all_fields = set(service_dict.keys()).union(set(client_dict.keys()))

            for field in all_fields:
                if field in service_dict and field in client_dict:
                    combined = self.combine_conditions(field, service_dict[field], client_dict[field])
                    if combined is None:
                        # 如果同一字段条件合并失败，说明无交集，整体查询不可行
                        return [], f"没有权限访问过滤条件: {client_dict[field]}"
                    else:
                        final_filters.append(combined)
                elif field in service_dict:
                    final_filters.append(service_dict[field])
                elif field in client_dict:
                    final_filters.append(client_dict[field])

            if not final_filters:
                return [],"没有权限访问过滤条件值"
            return final_filters, ""
        except Exception as e:
            error_msg = f"验证过滤条件时发生错误: {str(e)}"
            print(error_msg)
            return [], error_msg

    def get_table_privileges(self, tables: List[str], user_id: str) -> Tuple[Dict[str, Dict], str]:
        """
        从权限表中获取用户对指定表的权限
        
        Args:
            tables: 表名列表
            user_id: 用户ID
            
        Returns:
            Tuple[Dict[str, Dict], str]: 
                - 权限上下文字典，格式如：
                  {
                    "table_name": {
                        "allowed_columns": ["col1", "col2"],
                        "row_filter": ["col1 = 'value'", "col2 in ('v1', 'v2')"]
                    }
                  }
                - 错误信息
        """
        try:
            conn = self._get_connection()
            cursor = conn.cursor()
            
            # 构建表名的占位符
            table_placeholders = ','.join(['%s'] * len(tables))

            schema_prefix = self._get_schema_prefix()
            
            # 查询用户对这些表的权限
            if self.db_type == 'postgresql' or self.db_type == 'openGauss':
                query = f"""
                WITH user_auth AS (
                    SELECT 
                        da.id,
                        da.authorized_object_code as table_name,
                        array_agg(json_build_object('name', dai.item_code, 'description', dai.item_names)) as allowed_columns,
                        array_agg(
                            CASE 
                                WHEN dai.filter_operator IS NOT NULL AND dai.filter_value IS NOT NULL 
                                THEN json_build_object(
                                    'field', dai.item_code,
                                    'operator', dai.filter_operator,
                                    'value', dai.filter_value,
                                    'related_code_table', dai.dict_id,
                                    'code_field', split_part(dd.source_item, ',', 1),
                                    'desc_field', split_part(dd.source_item, ',', 2)
                                )
                            END
                        ) FILTER (WHERE dai.filter_operator IS NOT NULL AND dai.filter_value IS NOT NULL) as row_filters
                    FROM {schema_prefix}data_authorization da
                    LEFT JOIN {schema_prefix}data_authorization_item dai ON da.id = dai.data_authorization_id
                    left join {schema_prefix}ds_dictionary dd on dai.dict_id  = dd.dict_code
                    WHERE da.type = 'user'
                        AND da.allocation_object_id = %s
                        AND da.authorized_object_type = 'table'
                        --AND da.status = '已审核'
                    GROUP BY da.id, da.authorized_object_code
                )
                SELECT 
                    table_name,
                    allowed_columns,
                    row_filters
                FROM user_auth
                """
            elif self.db_type == 'mysql':
                query = f"""
                WITH user_auth AS (
                    SELECT 
                        da.id,
                        da.authorized_object_code AS table_name,
                        GROUP_CONCAT(DISTINCT dai.item_code) AS allowed_columns,
                        GROUP_CONCAT(
                            CASE 
                                WHEN dai.filter_operator IS NOT NULL AND dai.filter_value IS NOT NULL 
                                THEN JSON_OBJECT(
                                    'field', dai.item_code,
                                    'operator', dai.filter_operator,
                                    'value', dai.filter_value
                                )
                                ELSE NULL
                            END
                        ) AS row_filters
                    FROM {schema_prefix}data_authorization da
                    LEFT JOIN {schema_prefix}data_authorization_item dai 
                        ON da.id = dai.data_authorization_id
                    WHERE da.type = '用户'
                        AND da.allocation_object_id = %s
                        AND da.authorized_object_type = '表'
                        AND da.status = '已审核'
                    GROUP BY da.id, da.authorized_object_code
                )
                SELECT 
                    table_name,
                    allowed_columns,
                    row_filters
                FROM user_auth;
                """
            else:
                raise ValueError("不支持的数据库类型")
            
            # 执行查询
            cursor.execute(query, [user_id] + tables)
            privileges = cursor.fetchall()
            
            cursor.close()
            conn.close()
            
            return privileges, ""
            
        except Exception as e:
            error_msg = f"获取表权限时发生错误: {str(e)}"
            print(error_msg)
            return {}, error_msg
